package com.emser.unc.database.dao;

import java.util.ArrayList;
import java.util.Iterator;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.emser.unc.database.MySQLiteHelper;
import com.emser.unc.serializable.ProductSoldDTO;
import com.emser.unc.serializable.SaleReportDTO;

public class SaleReportDataSource {
	
	private SQLiteDatabase database;
	private MySQLiteHelper dbHelper;
	private String[] allColumns = { "sale_report_id", "customer_id",
			"seller_username", "amount" };

	public SaleReportDataSource(Context context) {
		dbHelper = new MySQLiteHelper(context);
	}
	
	public void open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}
	
	public void close() {
		dbHelper.close();
	}
	
	public SaleReportDTO createSaleReport(SaleReportDTO report) {
		ContentValues values = new ContentValues();
		SaleReportDTO reportRet = null;
		if (report != null) {
			values.put("customer_id", report.getCustomerID());
			values.put("seller_username", report.getSellerUserName());
			values.put("amount", 0);
			long insertId = database.insert(MySQLiteHelper.TABLE_SALE_REPORT,
					null, values);
			Cursor cursor = database.query(MySQLiteHelper.TABLE_SALE_REPORT,
					allColumns, "sale_report_id = " + insertId,
					null, null, null, null);
			cursor.moveToFirst();
			reportRet = cursorToSaleReport(cursor);
			cursor.close();
		}
		return reportRet;
	}
	
	public void deleteSaleReport(Long saleReportId) {		
		database.delete(MySQLiteHelper.TABLE_SALE_REPORT, "sale_report_id = " + saleReportId, null);
		database.delete(MySQLiteHelper.TABLE_SALE_ORDER, "sale_report_id = "
				+ saleReportId, null);
	}
	
	public Boolean isInDatabase(Long idReport){
		try {
			Boolean isInDatabase = Boolean.FALSE;
			String query = "SELECT * FROM " + MySQLiteHelper.TABLE_SALE_REPORT
					+ " WHERE sale_report_id = ?";
			String[] whereArgs = new String[] { idReport.toString() };
			Cursor cursor = database.rawQuery(query, whereArgs);
			if(cursor != null && cursor.getCount()>0){
				isInDatabase = Boolean.TRUE;
			}
			cursor.close();
			return isInDatabase;
		} catch (Exception e) {
			return false;
		}	
	}
	
	public Boolean isSent(Long idReport){
		try {
			Boolean isInDatabase = Boolean.FALSE;
			String query = "SELECT * FROM " + MySQLiteHelper.TABLE_SALE_REPORT_SENT
					+ " WHERE sale_report_id = ?";
			String[] whereArgs = new String[] { idReport.toString() };
			Cursor cursor = database.rawQuery(query, whereArgs);
			if(cursor != null && cursor.getCount()>0){
				isInDatabase = Boolean.TRUE;
			}
			cursor.close();
			return isInDatabase;
		} catch (Exception e) {
			return false;
		}	
	}
	
	public Boolean isPending(Long idReport){
		try {
			Boolean isInDatabase = Boolean.FALSE;
			String query = "SELECT * FROM " + MySQLiteHelper.TABLE_SALE_REPORT_PENDING
					+ " WHERE sale_report_id = ?";
			String[] whereArgs = new String[] { idReport.toString() };
			Cursor cursor = database.rawQuery(query, whereArgs);
			if(cursor != null && cursor.getCount()>0){
				isInDatabase = Boolean.TRUE;
			}
			cursor.close();
			return isInDatabase;
		} catch (Exception e) {
			return false;
		}	
	}
	
	public SaleReportDTO getSaleReport(Long saleReportId){
		SaleReportDTO saleReport = null;
		Cursor cursor = database.query(MySQLiteHelper.TABLE_SALE_REPORT,
				allColumns, "sale_report_id = " + saleReportId,
				null, null, null, null);
		cursor.moveToFirst();
		if(cursor != null){
			saleReport = new SaleReportDTO();
			saleReport = cursorToSaleReport(cursor);
		}
		
		cursor.close();
		return saleReport;
	}
	
	public void setPending(Long saleReportId){
		ContentValues values = new ContentValues();
		values.put("sale_report_id", saleReportId);
		database.insert(MySQLiteHelper.TABLE_SALE_REPORT_PENDING,null, values);
	}
	
	public void setSent(Long saleReportId){
		ContentValues values = new ContentValues();
		values.put("sale_report_id", saleReportId);
		database.insert(MySQLiteHelper.TABLE_SALE_REPORT_SENT,null, values);
	}
	
	public ArrayList<SaleReportDTO> getAllPendingReports(){	
		ArrayList<SaleReportDTO> listSaleReports = null;
		String query = "SELECT o.sale_report_id, o.customer_id, " +
				"o.seller_username, o.amount FROM "
			+ MySQLiteHelper.TABLE_SALE_REPORT
			+ " o JOIN "
			+ MySQLiteHelper.TABLE_SALE_REPORT_PENDING
			+ " p ON o.sale_report_id = p.sale_report_id";
		Cursor cursor = null;
		cursor = database.rawQuery(query, null);
		if(cursor != null){
			cursor.moveToFirst();
			listSaleReports = new ArrayList<SaleReportDTO>();
			while (!cursor.isAfterLast()) {
				SaleReportDTO saleReport;
				saleReport = cursorToSaleReport(cursor);
				listSaleReports.add(saleReport);
				cursor.moveToNext();
			}
			cursor.close();
		}
		return  listSaleReports;
	}
	
	public ArrayList<SaleReportDTO> getAllSentReports(){
		ArrayList<SaleReportDTO> listSaleReports = null;
		String query = "SELECT o.sale_report_id, o.customer_id, " +
				"o.seller_username, o.amount FROM "
			+ MySQLiteHelper.TABLE_SALE_REPORT
			+ " o JOIN "
			+ MySQLiteHelper.TABLE_SALE_REPORT_SENT
			+ " p ON o.sale_report_id = p.sale_report_id";
		Cursor cursor = null;
		cursor = database.rawQuery(query, null);
		if(cursor != null){
			cursor.moveToFirst();
			listSaleReports = new ArrayList<SaleReportDTO>();
			while (!cursor.isAfterLast()) {
				SaleReportDTO saleReport;
				saleReport = cursorToSaleReport(cursor);
				listSaleReports.add(saleReport);
				cursor.moveToNext();
			}
			cursor.close();
		}
		return  listSaleReports;
	}
	
	private ArrayList<ProductSoldDTO> getProductsSold(Long saleReportId){
		String[] saleOrderColumns = { MySQLiteHelper.COLUMN_ID, "product_id",
				"quantity", "total_price", "sale_report_id" };
		ArrayList<ProductSoldDTO> products = new ArrayList<ProductSoldDTO>();
		Cursor cursor = database.query(MySQLiteHelper.TABLE_SALE_ORDER,
				saleOrderColumns, "sale_report_id = "+saleReportId, null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			ProductSoldDTO cu = cursorToProductSold(cursor);
			products.add(cu);
			cursor.moveToNext();
		}
		cursor.close();
		return products;
	}
	
	private ProductSoldDTO cursorToProductSold(Cursor cursor) {
		ProductSoldDTO product = new ProductSoldDTO();
		product.setId(cursor.getLong(0));
		product.setCode(cursor.getLong(1));
		product.setQuantity(cursor.getLong(2));
		product.setTotalPrice(cursor.getDouble(3));
		String[] columns = {"title", "unit_price", "stock" };
		Cursor cursorp = database.query(MySQLiteHelper.TABLE_PRODUCT,
				columns, "product_id = " + product.getCode(),
				null, null, null, null);
		cursorp.moveToFirst();
		product.setTitle(cursorp.getString(0));
		product.setUnitPrice(cursorp.getDouble(1));
		product.setStock(cursorp.getLong(2));
		cursorp.close();
		return product;
	}
	
	private SaleReportDTO cursorToSaleReport(Cursor cursor) {
		SaleReportDTO saleReport = new SaleReportDTO();
		saleReport.setIdReport(cursor.getLong(0));
		saleReport.setCustomerID(cursor.getLong(1));
		saleReport.setSellerUserName(cursor.getString(2));
		saleReport.setAmount(cursor.getDouble(3));

		ArrayList<ProductSoldDTO> products = getProductsSold(saleReport.getIdReport());
		saleReport.setProductsSold(products);
		Iterator<ProductSoldDTO> i = products.iterator();
		Double amount = (double) 0;
		while (i.hasNext()) {
			ProductSoldDTO pd = i.next();
			amount = amount + pd.getTotalPrice();
		}
		saleReport.setAmount(amount);
		return saleReport;
	}
}
